#include <stdx/all.h>
#include <dbx/DBConnectPool.h>
#include <dbx/SQLiteConnect.h>

#define XG_SQLITE_COLUMN_MAXLEN	1024
#define DATETIME DateTime::ToString().c_str()

static char buffer[1024 * 1024];

static bool HasAlphabet(const char* str)
{
	while (*str)
	{
		if (*str >= 'a' && *str <= 'z') return true;
		if (*str >= 'A' && *str <= 'Z') return true;

		++str;
	}

	return false;
}
static string TransDateTime(const char* str)
{
	char buf[32];
	vector<string> vec;
	int hour, min, sec;
	int year, mon, date;
	string tmp = stdx::replace(str, "  ", " ");

	if (stdx::split(vec, tmp, " ") < 3)
	{
		return stdx::EmptyString();
	}

	if (vec[0] == "Jan") mon = 1;
	else if (vec[0] == "Feb") mon = 2;
	else if (vec[0] == "Mar") mon = 3;
	else if (vec[0] == "Apr") mon = 4;
	else if (vec[0] == "May") mon = 5;
	else if (vec[0] == "Jun") mon = 6;
	else if (vec[0] == "Jul") mon = 7;
	else if (vec[0] == "Aug") mon = 8;
	else if (vec[0] == "Sep") mon = 9;
	else if (vec[0] == "Oct") mon = 10;
	else if (vec[0] == "Nov") mon = 11;
	else if (vec[0] == "Dec") mon = 12;
	else return stdx::EmptyString();

	date = atoi(vec[1].c_str());
	year = atoi(vec[2].c_str());

	if (vec.size() < 4)
	{
		sprintf(buf, "%04d-%02d-%02d 00:00:00", year, mon, date);
	}
	else
	{
		sscanf(vec[3].c_str(), "%d:%d:%d", &hour, &min, &sec);

		if (strstr(str, "PM") || strstr(str, "pm"))
		{
			if (hour == 12) hour = 0;
			else hour += 12;
		}

		sprintf(buf, "%04d-%02d-%02d %02d:%02d:%02d", year, mon, date, hour, min, sec);
	}

	return buf;
}
static string GetTemplateConfigString()
{
	u_char data[] = {
		0x2D,0x2D,0x20,0xCA,0xFD,0xBE,0xDD,0xB5,0xBC,0xB3,0xF6,0xC5,0xE4,0xD6,0xC3,0x0D,
		0x0A,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xCA,0xC7,
		0xB7,0xF1,0xBD,0xAB,0xCA,0xFD,0xBE,0xDD,0xB2,0xE5,0xC8,0xEB,0xB1,0xED,0xD6,0xD0,
		0x0D,0x0A,0x49,0x4E,0x53,0x45,0x52,0x54,0x5F,0x52,0x45,0x43,0x4F,0x52,0x44,0x5F,
		0x46,0x4C,0x41,0x47,0x20,0x3D,0x20,0x74,0x72,0x75,0x65,0x0D,0x0A,0x0D,0x0A,0x2D,
		0x2D,0x20,0xCA,0xFD,0xBE,0xDD,0xBC,0xC7,0xC2,0xBC,0xD6,0xD0,0x4E,0x55,0x4C,0x4C,
		0xD7,0xD6,0xB6,0xCE,0xD6,0xB5,0x0D,0x0A,0x43,0x4F,0x4C,0x55,0x4D,0x4E,0x5F,0x4E,
		0x55,0x4C,0x4C,0x5F,0x53,0x54,0x52,0x49,0x4E,0x47,0x20,0x3D,0x20,0x22,0x4E,0x55,
		0x4C,0x4C,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xCA,0xFD,0xBE,0xDD,0xBC,0xC7,
		0xC2,0xBC,0xD7,0xD6,0xB6,0xCE,0xB7,0xD6,0xB8,0xF4,0xB1,0xEA,0xD6,0xBE,0x0D,0x0A,
		0x43,0x4F,0x4C,0x55,0x4D,0x4E,0x5F,0x53,0x50,0x4C,0x49,0x54,0x45,0x52,0x5F,0x53,
		0x54,0x52,0x49,0x4E,0x47,0x20,0x3D,0x20,0x22,0x5C,0x74,0x22,0x0D,0x0A,0x0D,0x0A,
		0x2D,0x2D,0x20,0xB5,0xBC,0xB3,0xF6,0xCA,0xFD,0xBE,0xDD,0xBC,0xC7,0xC2,0xBC,0xCE,
		0xC4,0xBC,0xFE,0xB8,0xF1,0xCA,0xBD,0x28,0x30,0xA3,0xBA,0x53,0x51,0x4C,0x49,0x54,
		0x45,0xCE,0xC4,0xBC,0xFE,0x20,0x31,0xA3,0xBA,0x53,0x51,0x4C,0xBD,0xC5,0xB1,0xBE,
		0x20,0x32,0xA3,0xBA,0xCE,0xC4,0xB1,0xBE,0xCE,0xC4,0xBC,0xFE,0x29,0x0D,0x0A,0x45,
		0x58,0x50,0x4F,0x52,0x54,0x5F,0x52,0x45,0x43,0x4F,0x52,0x44,0x46,0x49,0x4C,0x45,
		0x5F,0x46,0x4F,0x52,0x4D,0x41,0x54,0x20,0x3D,0x20,0x30,0x0D,0x0A,0x0D,0x0A,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xCA,0xFD,0xBE,0xDD,
		0xBF,0xE2,0xC1,0xAC,0xBD,0xD3,0xB3,0xD8,0xC5,0xE4,0xD6,0xC3,0x0D,0x0A,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xCA,0xFD,0xBE,0xDD,0xBF,
		0xE2,0xC1,0xAC,0xBD,0xD3,0xB3,0xD8,0xB6,0xAF,0xCC,0xAC,0xBF,0xE2,0xC2,0xB7,0xBE,
		0xB6,0x0D,0x0A,0x44,0x4C,0x4C,0x50,0x41,0x54,0x48,0x20,0x3D,0x20,0x22,0x24,0x48,
		0x4F,0x4D,0x45,0x2F,0x70,0x72,0x6F,0x64,0x75,0x63,0x74,0x2F,0x64,0x6C,0x6C,0x2F,
		0x6C,0x69,0x62,0x64,0x62,0x78,0x2E,0x6D,0x79,0x73,0x71,0x6C,0x70,0x6F,0x6F,0x6C,
		0x2E,0x73,0x6F,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xBC,0xE0,0xCC,0xFD,0xB6,
		0xCB,0xBF,0xDA,0x0D,0x0A,0x50,0x4F,0x52,0x54,0x20,0x3D,0x20,0x33,0x33,0x30,0x36,
		0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xD6,0xF7,0xBB,0xFA,0xB5,0xD8,0xD6,0xB7,0x0D,
		0x0A,0x48,0x4F,0x53,0x54,0x20,0x3D,0x20,0x22,0x31,0x32,0x37,0x2E,0x30,0x2E,0x30,
		0x2E,0x31,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xCA,0xFD,0xBE,0xDD,0xBF,0xE2,
		0xC3,0xFB,0xB3,0xC6,0x0D,0x0A,0x4E,0x41,0x4D,0x45,0x20,0x3D,0x20,0x22,0x78,0x75,
		0x6E,0x67,0x65,0x6E,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xD3,0xC3,0xBB,0xA7,
		0xC3,0xFB,0xB3,0xC6,0x0D,0x0A,0x55,0x53,0x45,0x52,0x20,0x3D,0x20,0x22,0x78,0x75,
		0x6E,0x67,0x65,0x6E,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x20,0xD3,0xC3,0xBB,0xA7,
		0xC3,0xDC,0xC2,0xEB,0x0D,0x0A,0x50,0x41,0x53,0x53,0x57,0x4F,0x52,0x44,0x20,0x3D,
		0x20,0x22,0x78,0x75,0x6E,0x67,0x65,0x6E,0x22,0x0D,0x0A,0x0D,0x0A,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,0x2D,
		0x2D,0x2D,0x2D,0x0D,0x0A,0x0D,0x0A,0x00
	};

	return (char*)(data);
}


class DBImportApplication : public Application
{
protected:
	int errcnt;
	TextFile out;
	bool imported;
	bool inserted;
	string dbname;
	string exepath;
	string tabname;
	string nulltag;
	string spliter;
	LogThread* log;
	ConfigFile cfg;
	string selectsql;
	string scriptpath;
	sp<DBConnect> conn;
	bool begintransaction;
	DBConnectPool* dbconnpool;

protected:
	void logDBError(DBConnect* conn)
	{
		log->printf("\t\tERR[%d] DESC[%s]\n", conn->getErrorCode(), conn->getErrorString().c_str());
	}
	void printErrorString(const string& msg)
	{
		SetConsoleTextColor(eRED);
		puts(msg.c_str());
		SetConsoleTextColor(eWHITE);
		log->printf("%s|ERR|%s\n", DATETIME, msg.c_str());
	}
	void printSuccessString(int percent, const string& msg)
	{
		static int val = XG_ERROR;

		if (val == percent && percent < 100) return;
		print(val = percent, msg.c_str(), "SUC");
	}
	void print(int percent, const char* msg, const char* tag)
	{
		log->printf("%s|%s|%3d %% 100 %s\n", DATETIME, tag, percent, msg);

		if (tag == "SUC") SetConsoleTextColor(eGREEN);
		else if (tag == "ERR") SetConsoleTextColor(eRED);
		else if (tag == "IMP") SetConsoleTextColor(eYELLOW);
		printf("%3d %% 100 %s\n", percent, msg);
		SetConsoleTextColor(eWHITE);
	}

	bool connect()
	{
		CHECK_FALSE_RETURN(getDBConnectPool(cfg) && (conn = dbconnpool->get()));

		return true;
	}
	bool loadConfig()
	{
		string sql;
		string path;
		vector<string> vec;

		inserted = true;

		if (GetCmdParam("-c")) path = GetCmdParam("-c");
		if (path.empty()) path = exepath + "/config.lua";

		CHECK_FALSE_RETURN(cfg.open(path));
		CHECK_FALSE_RETURN(cfg.getVariable("NAME", dbname) && connect());
		
		tabname = GetCmdParam("-t");

		cfg.getVariable("COLUMN_NULL_STRING", nulltag);
		cfg.getVariable("INSERT_RECORD_FLAG", inserted);
		cfg.getVariable("COLUMN_SPLITER_STRING", spliter);

		spliter = stdx::replace(spliter, "\\t", "\t");
		spliter = stdx::replace(spliter, "\\r", "\r");
		spliter = stdx::replace(spliter, "\\n", "\n");

		if (GetCmdParam("-select") == NULL)
		{
			CHECK_FALSE_RETURN(conn->getPrimaryKeys(vec, tabname) > 0);
		}

		return true;
	}
	bool getDBConnectPool(const ConfigFile& file)
	{
		string dllname;
		string dbdllpath;
		static DllFile dbconnpooldll;
		CREATE_DBCONNECTPOOL_FUNC crtfunc;

		CHECK_FALSE_RETURN(file.getVariable("DLLPATH", dbdllpath));

		if ((dllname = path::name(dbdllpath)) == dbdllpath)
		{
			if (path::size(dbdllpath = exepath + "/" + dllname) <= 0)
			{
				dbdllpath = exepath + "/lib/" + dllname;
			}
		}

		CHECK_FALSE_RETURN(dbconnpooldll.open(dbdllpath) && dbconnpooldll.read(crtfunc, "CreateDBConnectPool"));
		CHECK_FALSE_RETURN((dbconnpool = crtfunc()) && dbconnpool->init(file));

		return true;
	}
	string getCreateSQL(const vector<ColumnData>& cols, DBConnect* conn)
	{
		string sql = "create table " + tabname + "(";

		for (size_t i = 0; i < cols.size(); i++)
		{
			const ColumnData& item = cols[i];

			sql += item.name;
			sql += " ";

			if (item.type == DBData_Integer)
			{
				sql += "int";
			}
			else if (item.type == DBData_Float)
			{
				sql += "double";
			}
			else if (item.type == DBData_DateTime)
			{
				sql += "datetime";
			}
			else
			{
				if (item.size <= 0)
				{
					stdx::append(sql, "varchar(%d)", XG_SQLITE_COLUMN_MAXLEN);
				}
				else
				{
					stdx::append(sql, "varchar(%d)", item.size);
				}
			}

			if (i + 1 < cols.size())
			{
				sql += ",";
			}
		}

		sql += ")";

		return sql;
	}
	string createBatchInsertSQL(DBConnect* conn, string& sql, int fields = 0)
	{
		static int cnt = 0;
		static size_t pos = 0;
		static string batch_sql;
		static int batch_size = 0;

		if (batch_size == 1) return sql;

		if (batch_size == 0)
		{
			cfg.getVariable("INSERT_BATCH_SIZE", batch_size);

			if (batch_size <= 0)
			{
				batch_size = 1;
			}
		}

		if (sql.empty())
		{
			sql = batch_sql; batch_sql.clear(); cnt = 0;

			return sql;
		}

		if (batch_sql.empty())
		{
			batch_sql = sql;
		}
		else
		{
			if (pos == 0) pos = sql.find(") values(") + 8;

			batch_sql += ","; batch_sql += sql.c_str() + pos;
		}

		if (0 == strcmp(conn->getSystemName(), "SQLite"))
		{
			if (fields > 500 || ++cnt >= batch_size || batch_sql.length() > 1024 * 1024)
			{
				sql = batch_sql; batch_sql.clear(); cnt = 0;

				return sql;
			}
		}
		else
		{
			if (fields > 50000 || ++cnt >= batch_size || batch_sql.length() > 1024 * 1024)
			{
				sql = batch_sql; batch_sql.clear(); cnt = 0;

				return sql;
			}
		}

		return stdx::EmptyString();
	}
	string getInsertSQL(vector<DBData*>& vec, const vector<ColumnData>& cols, vector<string>& vals, DBConnect* conn)
	{
		string sql;
		DBDateTime dt;
		static string hdr;
		
		if (hdr.empty())
		{
			sql = "insert into " + tabname + "(";

			for (size_t i = 0; i < cols.size(); i++)
			{
				sql += cols[i].name;

				if (i + 1 < cols.size())
				{
					sql += ",";
				}
			}

			sql += ") values(";

			hdr = sql;
		}
		
		sql = hdr;

		for (size_t i = 0; i < cols.size(); i++)
		{
			const ColumnData& item = cols[i];

			if (vals[i].empty() || string::npos != nulltag.find(vals[i]))
			{
				sql += "NULL";
			}
			else if (item.type == DBData_DateTime)
			{
				dt = HasAlphabet(vals[i].c_str()) ? (vals[i] = TransDateTime(vals[i].c_str())) : vals[i];

				sql += dt.toValueString(conn->getSystemName());
			}
			else if (item.type == DBData_Integer || item.type == DBData_Float)
			{
				sql += vals[i];
			}
			else
			{
				static bool binded = GetCmdParam("-bc") ? true : false;

				if (binded && inserted && scriptpath.empty())
				{
					sql += conn->getBindString();

					vec.push_back(new DBString(vals[i]));
				}
				else
				{
					sql += "\'" + vals[i] + "\'";
				}
			}

			if (i + 1 < cols.size())
			{
				sql += ",";
			}
		}

		sql += ")";

		return sql;
	}
	bool ExportDB(DBConnect* conn, const char* path)
	{
		int res;
		int sz = 0;
		int format = 0;
		int linenum = 0;
		static int idx = 0;

		string sql;
		string msg;
		string line;
		ifstream file;
		ColumnData item;
		vector<string> vec;
		sp<QueryResult> rs;
		vector<ColumnData> cols;

		sp<RowData> row;
		SQLiteConnect sqlite;
		vector<DBData*> datas;

		selectsql.clear();

		cfg.getVariable("EXPORT_RECORDFILE_FORMAT", format);

		if (format < 0 || format > 2)
		{
			format = 0;
		}

		if (GetCmdParam("-select"))
		{
			selectsql = GetCmdParam("-select"); sql = "select count(*) from(" + selectsql + ") t";
		}
		else
		{
			sql = "select count(*) from " + tabname;
		}

		if (!(rs = conn->query(sql)))
		{
			printErrorString("database error, aborting task ...");

			return false;
		}

		if (!(row = rs->next()))
		{
			printErrorString("database error, aborting task ...");
			conn->release(rs);

			return false;
		}

		if ((sz = row->getInt(0)) < 0)
		{
			printErrorString("database error, aborting task ...");
			conn->release(rs);

			return false;
		}

		conn->release(rs);

		if (sz == 0)
		{
			SetConsoleTextColor(eYELLOW);
			puts("there is no record to export");
			SetConsoleTextColor(eWHITE);
			errcnt = 0;

			return true;
		}

		if (GetCmdParam("-i") && path::size(path) > 0)
		{
			CHECK_FALSE_RETURN(cmdx::CheckCommand("file[%s] exists, overwrite or not ? (y/n)", path::name(path).c_str()));
		}

		if (selectsql.empty())
		{
			sql = "select * from " + tabname;
		}
		else
		{
			sql = selectsql;
		}

		if (!(rs = conn->quickQuery(sql)))
		{
			printErrorString("database error, aborting task ...");

			return false;
		}

		for (int i = 0, cnt = rs->cols(); i < cnt; i++)
		{
			if (rs->getColumnData(item, i))
			{
				cols.push_back(item);
			}
			else
			{
				printErrorString("database error, aborting task ...");
				conn->release(rs);

				return false;
			}
		}

		if (format > 0)
		{
			scriptpath = path;
			out.open(path, true);
			out.setAutoFlush(false);
		}
		else
		{
			if (sqlite.connect(path))
			{
				if (sqlite.getPrimaryKeys(vec, tabname) < 0)
				{
					sql = getCreateSQL(cols, &sqlite);

					if (sqlite.execute(sql) < 0)
					{
						printErrorString("system error, aborting task ...");
						conn->release(rs);

						return false;
					}
				}
				
				if (Process::GetCmdParam("-clear") || (Process::GetCmdParam("-i") && cmdx::CheckCommand("clear table[%s] or not ? (y/n)", tabname.c_str())))
				{
					sql = "delete from " + tabname;

					if (sqlite.execute(sql) < 0)
					{
						printErrorString("clear table[" + tabname + "] failed");

						return false;
					}
				}
			}
			else
			{
				printErrorString("system error, aborting task ...");
				conn->release(rs);

				return false;
			}
		}

		errcnt = 0;

		while (linenum < sz)
		{
			if (!(row = rs->next())) break;

			++linenum;

			if (format == 2)
			{
				string val = row->getString(0, buffer, sizeof(buffer));

				if (row->isNull())
				{
					msg = nulltag;
				}
				else
				{
					msg = val;
				}

				for (size_t i = 1; i < cols.size(); i++)
				{
					val = row->getString(i, buffer, sizeof(buffer));

					if (row->isNull())
					{
						msg += spliter + nulltag;
					}
					else
					{
						msg += spliter + val;
					}
				}

				if (string::npos == msg.find('\n'))
				{
					out.puts(msg.c_str());

					stdx::format(msg, "write record success at %d line", linenum);
					printSuccessString(linenum * 100 / sz, msg.c_str());
				}
				else
				{
					++errcnt;

					stdx::format(msg, "find illegal character at %d line", linenum);
					print(linenum * 100 / sz, msg.c_str(), "IMP");

					if (Process::GetCmdParam("-i"))
					{
						CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)") || conn->release(rs));
					}
				}
			}
			else
			{
				vec.clear();

				for (size_t i = 0; i < cols.size(); i++)
				{
					const ColumnData& item = cols[i];

					if (item.type == DBData_DateTime)
					{
						DateTime dt; row->getDateTime(dt, i); vec.push_back(dt.toString());
					}
					else
					{
						vec.push_back(row->getString(i, buffer, sizeof(buffer)));
					}

					if (row->isNull())
					{
						vec[i].clear();
					}
				}

				sql = getInsertSQL(datas, cols, vec, format > 0 ? conn : &sqlite);

				if (format == 1)
				{
					if (string::npos == sql.find('\n'))
					{
						msg = sql + ";";
						out.puts(msg.c_str());

						stdx::format(msg, "write insert sql script success at %d line", linenum);
						printSuccessString(linenum * 100 / sz, msg.c_str());
					}
					else
					{
						++errcnt;

						stdx::format(msg, "find illegal character at %d line", linenum);
						print(linenum * 100 / sz, msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)") || conn->release(rs));
						}
					}
				}
				else
				{
					if ((sql = createBatchInsertSQL(&sqlite, sql, datas.size())).empty())
					{
						continue;
					}

					if (idx++ % 1000 == 0) sqlite.begin(true);

__EXPORT_SQLITE_EXEC__:
					res = sqlite.execute(sql, datas);

					for (size_t i = 0; i < datas.size(); i++)
					{
						delete datas[i];
					}

					datas.clear();

					if (res > 0)
					{
						stdx::format(msg, "insert into %s success at %d line", tabname.c_str(), linenum);
						printSuccessString(linenum * 100 / sz, msg.c_str());
					}
					else if (res == SQLRtn_Duplicate)
					{
						stdx::format(msg, "insert into %s duplicate at %d line", tabname.c_str(), linenum);
						print(linenum * 100 / sz, msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)") || conn->release(rs));
						}
					}
					else
					{
						++errcnt;

						stdx::format(msg, "insert into %s error at %d line", tabname.c_str(), linenum);
						print(linenum * 100 / sz, msg.c_str(), "ERR");
						logDBError(&sqlite);

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)") || conn->release(rs));
						}
					}
				}
			}
		}

		conn->release(rs);

		if (format == 0)
		{
			if (sql.empty() && (sql = createBatchInsertSQL(&sqlite, sql)).length() > 0)
			{
				goto __EXPORT_SQLITE_EXEC__;
			}

			sqlite.commit();
		}

		return true;
	}
	bool ImportDB(DBConnect* conn, const char* path)
	{
		string sql;
		string msg;
		string line;
		ifstream file;
		ColumnData item;
		vector<string> vec;
		sp<QueryResult> rs;
		vector<ColumnData> cols;

		int res;
		int linenum = 0;
		long long readed = 0;
		bool chkflag = false;
		SQLiteConnect sqlite;
		vector<DBData*> datas;
		long long sz = path::size(path);

		if (sz < 0)
		{
			printErrorString("system error, aborting task ...");

			return false;
		}

		if (sz == 0)
		{
			SetConsoleTextColor(eYELLOW);
			puts("there is no record to import");
			SetConsoleTextColor(eWHITE);

			errcnt = 0;

			return true;
		}

		sql = "select * from " + tabname + " where 1 = 0";

		if (!(rs = conn->query(sql)))
		{
			printErrorString("database error, aborting task ...");

			return false;
		}

		for (int i = 0, cnt = rs->cols(); i < cnt; i++)
		{
			if (rs->getColumnData(item, i))
			{
				cols.push_back(item);
			}
			else
			{
				printErrorString("database error, aborting task ...");

				conn->release(rs);

				return false;
			}
		}

		conn->release(rs);

		if (inserted)
		{
			if (Process::GetCmdParam("-clear") || (Process::GetCmdParam("-i") && cmdx::CheckCommand("clear table[%s] or not ? (y/n)", tabname.c_str())))
			{
				sql = "delete from " + tabname;

				if (conn->execute(sql) < 0)
				{
					printErrorString("clear table[" + tabname + "] failed");

					return false;
				}
			}
		}
		else
		{
			out.open(scriptpath, true);
			out.setAutoFlush(false);
		}

		if (sqlite.connect(path) && sqlite.getPrimaryKeys(vec, tabname) >= 0)
		{
			vec.clear();
			sz = 0;
		}
		else
		{
			file.open(path);
		}

		if (sz > 0)
		{
			errcnt = 0;

			while (getline(file, line))
			{
				++linenum;

				if ((readed = file.tellg()) < 0)
				{
					printErrorString("system error, aborting task ...");

					return false;
				}

				if ((line = stdx::trim(line)).empty()) continue;

				stdx::split(vec, line, spliter);

				if (vec.size() < cols.size()) continue;

				sql = getInsertSQL(datas, cols, vec, conn);

				if (inserted)
				{
					/* Oracle Don't Support Batch Insert */
					if (strcmp(conn->getSystemName(), "Oracle"))
					{
						sql = createBatchInsertSQL(conn, sql, datas.size());

						if (sql.empty()) continue;
					}

__IMPORT_FILE_EXEC__:
					if (begintransaction)
					{
						res = conn->execute(sql, datas);
					}
					else
					{
						static int idx = 0; if (idx++ % 1000 == 0) conn->begin(idx > 1);

						res = conn->execute(sql, datas);
					}

					for (size_t i = 0; i < datas.size(); i++)
					{
						delete datas[i];
					}

					datas.clear();

					if (res > 0)
					{
						stdx::format(msg, "insert into %s success at %d line", tabname.c_str(), linenum);
						printSuccessString((int)(readed * 100 / sz), msg.c_str());
					}
					else if (res == SQLRtn_Duplicate)
					{
						stdx::format(msg, "insert into %s duplicate at %d line", tabname.c_str(), linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
					else
					{
						++errcnt;

						stdx::format(msg, "insert into %s error at %d line", tabname.c_str(), linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "ERR");
						logDBError(conn);

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
				}
				else
				{
					assert(datas.empty());

					if (string::npos == sql.find('\n'))
					{
						msg = sql + ";";
						out.puts(msg.c_str());

						stdx::format(msg, "write insert sql script at %d line", linenum);
						printSuccessString((int)(readed * 100 / sz), msg.c_str());
					}
					else
					{
						stdx::format(msg, "find illegal character at %d line", linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
				}
			}

			if (inserted && sql.empty())
			{
				if ((sql = createBatchInsertSQL(conn, sql)).empty())
				{
					stdx::format(msg, "insert into %s success at %d line", tabname.c_str(), linenum);
					printSuccessString((int)(readed * 100 / sz), msg.c_str());
				}
				else
				{
					goto __IMPORT_FILE_EXEC__;
				}
			}
			else if (sql.empty())
			{
				stdx::format(msg, "write insert sql script at %d line", linenum);
				printSuccessString((int)(readed * 100 / sz), msg.c_str());
			}
		}
		else
		{
			sp<RowData> row;

			sql = "select count(*) from " + tabname;

			if (!(rs = sqlite.query(sql)))
			{
				printErrorString("system error, aborting task ...");

				return false;
			}

			if (!(row = rs->next()))
			{
				printErrorString("system error, aborting task ...");

				return false;
			}

			if ((sz = row->getInt(0)) <= 0)
			{
				printErrorString("system error, aborting task ...");

				return false;
			}

			sql = "select * from " + tabname;

			rs->close();
			
			if (!(rs = sqlite.query(sql)))
			{
				printErrorString("system error, aborting task ...");

				return false;
			}

			errcnt = 0;

			while (readed < sz)
			{
				if (!(row = rs->next())) break;

				readed = ++linenum;

				vec.clear();

				for (size_t i = 0; i < cols.size(); i++)
				{
					vec.push_back(row->getString(i, buffer, sizeof(buffer)));
				}

				sql = getInsertSQL(datas, cols, vec, conn);

				if (inserted)
				{
					/* Oracle Don't Support Batch Insert */
					if (strcmp(conn->getSystemName(), "Oracle"))
					{
						if ((sql = createBatchInsertSQL(conn, sql, datas.size())).empty()) continue;
					}

__IMPORT_SQLITE_EXEC__:
					if (begintransaction)
					{
						res = conn->execute(sql, datas);
					}
					else
					{
						static int idx = 0; if (idx++ % 1000 == 0) conn->begin(idx > 1);

						res = conn->execute(sql, datas);
					}

					for (size_t i = 0; i < datas.size(); i++)
					{
						delete datas[i];
					}

					datas.clear();

					if (res > 0)
					{
						stdx::format(msg, "insert into %s success at %d line", tabname.c_str(), linenum);
						printSuccessString((int)(readed * 100 / sz), msg.c_str());
					}
					else if (res == SQLRtn_Duplicate)
					{
						stdx::format(msg, "insert into %s duplicate at %d line", tabname.c_str(), linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
					else
					{
						++errcnt;

						stdx::format(msg, "insert into %s error at %d line", tabname.c_str(), linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "ERR");
						logDBError(conn);

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
				}
				else
				{
					if (string::npos == sql.find('\n'))
					{
						msg = sql + ";";
						out.puts(msg.c_str());

						stdx::format(msg, "write insert sql script at %d line", linenum);
						printSuccessString((int)(readed * 100 / sz), msg.c_str());
					}
					else
					{
						stdx::format(msg, "find illegal character at %d line", linenum);
						print((int)(readed * 100 / sz), msg.c_str(), "IMP");

						if (Process::GetCmdParam("-i"))
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("continue or not ? (y/n)"));
						}
					}
				}
			}

			if (inserted && sql.empty())
			{
				if ((sql = createBatchInsertSQL(conn, sql)).empty())
				{
					stdx::format(msg, "insert into %s success at %d line", tabname.c_str(), linenum);
					printSuccessString((int)(readed * 100 / sz), msg.c_str());
				}
				else
				{
					goto __IMPORT_SQLITE_EXEC__;
				}
			}
			else if (sql.empty())
			{
				stdx::format(msg, "write insert sql script at %d line", linenum);
				printSuccessString((int)(readed * 100 / sz), msg.c_str());
			}
		}

		return true;
	}
	
public:
	DBImportApplication() : log(LogThread::Instance())
	{
		begintransaction = false;
		errcnt = -1;
	}

	void printTips()
	{
		puts("  dbimport parameter");
		puts("----------------------------------------------------------------");
		puts("  -t      : table name");
		puts("  -i      : interactive model");
		puts("  -c      : configure filename");
		puts("  -bc     : bind column when import");
		puts("  -etc    : export template configure");
		puts("----------------------------------------------------------------");
		puts("  -exec   : exec sql script");
		puts("  -clear  : clear data record");
		puts("  -select : select sql script");
		puts("  -import : import record filename [required when import record]");
		puts("  -export : export record filename [required when export record]");
		puts("----------------------------------------------------------------");
		puts("");
	}
	bool main()
	{
		const char* filepath;
		const char* cmd = NULL;

		CHECK_FALSE_RETURN(Process::GetProcessExePath(exepath));

		if (GetCmdParamCount() <= 1 || GetCmdParam("?") || GetCmdParam("--help"))
		{
			printTips();

			return true;
		}

		if ((cmd = GetCmdParam("-etc")))
		{
			if (*cmd)
			{
				if (GetCmdParam("-i") && path::size(cmd) > 0)
				{
					CHECK_FALSE_RETURN(cmdx::CheckCommand("file[%s] exists, overwrite or not ? (y/n)", path::name(cmd).c_str()));
				}

				if (out.open(cmd, true))
				{
					out.puts(GetTemplateConfigString());

					puts("export template configure success");
				}
				else
				{
					puts("export template configure failed");

					return false;
				}
			}
			else
			{
				puts(GetTemplateConfigString().c_str());
			}

			return true;
		}

		exepath = path::parent(exepath);
		Process::RegisterLibraryPath(string(exepath + "/lib").c_str());

		if (NULL == GetCmdParam("-t") || 0 == *GetCmdParam("-t") || '-' == *GetCmdParam("-t"))
		{
			puts("please input table name [-t tablename]");

			goto __ERROR__;
		}

		if (filepath = GetCmdParam("-import"))
		{
			imported = true;
		}
		else if (filepath = GetCmdParam("-export"))
		{
			imported = false;
		}
		else
		{
			puts("please input record file [-import path][-export path]");

			goto __ERROR__;
		}

		if (loadConfig())
		{
			string logpath;

			if ((cmd = GetCmdParam("-l")))
			{
				logpath = stdx::replace(cmd, "\\", "/");
			}
			else
			{
				logpath = "log";
			}

			log->init(logpath);

			printf(" DBTYPE : %s\n", conn->getSystemName());
			printf(" DBNAME : %s\n", dbname.c_str());

			log->printf("\n DBTYPE : %s\n", conn->getSystemName());
			log->printf(" DBNAME : %s\n", dbname.c_str());

			if (imported)
			{
				if (inserted)
				{
					if (Process::GetCmdParam("-i") && cmdx::CheckCommand("begin database transaction or not ? (y/n)"))
					{
						if (conn->begin(false))
						{
							begintransaction = true;
						}
						else
						{
							CHECK_FALSE_RETURN(cmdx::CheckCommand("start transaction failed, continue or not ? (y/n)"));
						}
					}
				}
				else
				{
					if (NULL == GetCmdParam("-o") || 0 == *GetCmdParam("-o") || '-' == *GetCmdParam("-o"))
					{
						puts("please input script output path [-o path]");

						goto __ERROR__;
					}
				}

				printf(" IMPORT : %s\n", path::name(filepath).c_str());
				puts("-------------------------------------------------------------------");

				log->printf(" IMPORT : %s\n", path::name(filepath).c_str());
				log->printf("------------------------------------------------------------------------------------\n");

				ImportDB(conn.get(), GetCmdParam("-import"));

				if (begintransaction)
				{
					if (cmdx::CheckCommand("commit database transaction or not ? (y/n)"))
					{
						conn->commit();
					}
					else
					{
						conn->rollback();
					}
				}
				else
				{
					conn->commit();
				}
			}
			else
			{
				printf(" EXPORT : %s\n", path::name(filepath).c_str());
				puts("-------------------------------------------------------------------");

				log->printf(" EXPORT : %s\n", path::name(filepath).c_str());
				log->printf("------------------------------------------------------------------------------------\n");

				inserted = true;

				ExportDB(conn.get(), GetCmdParam("-export"));
			}

			puts("-------------------------------------------------------------------\n");
	
			if (errcnt == 0)
			{
				if (cmd = GetCmdParam("-exec"))
				{
					if (conn->execute(cmd) >= 0)
					{
						SetConsoleTextColor(eGREEN);
						printf("exec sql[%s] success\n\n", cmd);
						log->printf("%s|IMP|exec sql[%s] success\n", DATETIME, cmd);

						conn->commit();
					}
					else
					{
						++errcnt;

						SetConsoleTextColor(eRED);
						printf("exec sql[%s] failed\n\n", cmd);
						log->printf("%s|ERR|exec sql[%s] failed\n", DATETIME, cmd);
						logDBError(conn.get());

						conn->rollback();
					}

					SetConsoleTextColor(eWHITE);
				}
			}

			if (Process::IsStartedByExplorer())
			{
				SystemPause(NULL);
			}

			log->wait();

			return (errcnt == 0);
		}

		if (dbname.empty())
		{
			puts("load configure failed");
		}
		else if (tabname.empty())
		{
			puts("connect the database failed");
		}
		else
		{
			printf("table '%s' not found\n", tabname.c_str());
		}

	__ERROR__:

		if (Process::IsStartedByExplorer())
		{
			SystemPause(NULL);
		}

		return false;
	}
};


START_APP(DBImportApplication)
